
--- Annual Summary ---
(
SELECT 
	year AS year,
	COUNT(MCR_payment_AB) as Nbene_AB, sum(MCR_payment_AB) as MCR_payment_AB, sum(BENE_payment_AB) as BENE_payment_AB, 
	COUNT(MCR_payment_A) as Nbene_A, sum(MCR_payment_A) as MCR_payment_A, sum(BENE_payment_A) as BENE_payment_A, 
	COUNT(MCR_payment_B) as Nbene_B, sum(MCR_payment_B) as MCR_payment_B, sum(BENE_payment_B) as BENE_payment_B, 
	COUNT(MCR_payment_ABD) as Nbene_ABD, sum(MCR_payment_ABD) as MCR_payment_ABD, sum(BENE_payment_ABD) as BENE_payment_ABD, 
	COUNT(MCR_payment_IP) as Nbene_IP, sum(MCR_payment_IP) as MCR_payment_IP, sum(BENE_payment_IP) as BENE_payment_IP, 
	COUNT(MCR_payment_SNF) as Nbene_SNF, sum(MCR_payment_SNF) as MCR_payment_SNF, sum(BENE_payment_SNF) as BENE_payment_SNF, 
	COUNT(MCR_payment_HHA_A) as Nbene_HHA_A, sum(MCR_payment_HHA_A) as MCR_payment_HHA_A, sum(BENE_payment_HHA_A) as BENE_payment_HHA_A, 
	COUNT(MCR_payment_HHA_B) as Nbene_HHA_B, sum(MCR_payment_HHA_B) as MCR_payment_HHA_B, sum(BENE_payment_HHA_B) as BENE_payment_HHA_B, 
	COUNT(MCR_payment_hospice) as Nbene_hospice, sum(MCR_payment_hospice) as MCR_payment_hospice, sum(BENE_payment_hospice) as BENE_payment_hospice, 
	COUNT(MCR_payment_carrier) as Nbene_carrier, sum(MCR_payment_carrier) as MCR_payment_carrier, sum(BENE_payment_carrier) as BENE_payment_carrier, 
	COUNT(MCR_payment_DME) as Nbene_DME, sum(MCR_payment_DME) as MCR_payment_DME, sum(BENE_payment_DME) as BENE_payment_DME, 
	COUNT(MCR_payment_nCBP) as Nbene_nCBP, sum(MCR_payment_nCBP) as MCR_payment_nCBP, sum(BENE_payment_nCBP) as BENE_payment_nCBP, 
	COUNT(MCR_payment_CBP) as Nbene_CBP, sum(MCR_payment_CBP) as MCR_payment_CBP, sum(BENE_payment_CBP) as BENE_payment_CBP, 
	COUNT(MCR_payment_OP) as Nbene_OP, sum(MCR_payment_OP) as MCR_payment_OP, sum(BENE_payment_OP) as BENE_payment_OP, 
	COUNT(MCR_payment_pde) as Nbene_pde, sum(MCR_payment_pde) as MCR_payment_pde, sum(BENE_payment_pde) as BENE_payment_pde
FROM (
	SELECT 
		t0.BENE_ID AS BENE_ID,
		t0.year AS year,
		sum(MCR_payment_AB) as MCR_payment_AB, sum(BENE_payment_AB) as BENE_payment_AB, 
		sum(MCR_payment_A) as MCR_payment_A, sum(BENE_payment_A) as BENE_payment_A, 
		sum(MCR_payment_B) as MCR_payment_B, sum(BENE_payment_B) as BENE_payment_B, 
		sum(MCR_payment_ABD) as MCR_payment_ABD, sum(BENE_payment_ABD) as BENE_payment_ABD, 
		sum(MCR_payment_IP) as MCR_payment_IP, sum(BENE_payment_IP) as BENE_payment_IP, 
		sum(MCR_payment_SNF) as MCR_payment_SNF, sum(BENE_payment_SNF) as BENE_payment_SNF, 
		sum(MCR_payment_HHA_A) as MCR_payment_HHA_A, sum(BENE_payment_HHA_A) as BENE_payment_HHA_A, 
		sum(MCR_payment_HHA_B) as MCR_payment_HHA_B, sum(BENE_payment_HHA_B) as BENE_payment_HHA_B, 
		sum(MCR_payment_hospice) as MCR_payment_hospice, sum(BENE_payment_hospice) as BENE_payment_hospice, 
		sum(MCR_payment_carrier) as MCR_payment_carrier, sum(BENE_payment_carrier) as BENE_payment_carrier, 
		sum(MCR_payment_DME) as MCR_payment_DME, sum(BENE_payment_DME) as BENE_payment_DME, 
		sum(MCR_payment_nCBP) as MCR_payment_nCBP, sum(BENE_payment_nCBP) as BENE_payment_nCBP, 
		sum(MCR_payment_CBP) as MCR_payment_CBP, sum(BENE_payment_CBP) as BENE_payment_CBP, 
		sum(MCR_payment_OP) as MCR_payment_OP, sum(BENE_payment_OP) as BENE_payment_OP, 
		sum(MCR_payment_pde) as MCR_payment_pde, sum(BENE_payment_pde) as BENE_payment_pde
	FROM
	(
	SELECT 
		BENE_ID AS BENE_ID,
		year AS year,
		month AS month,
		MCR_payment_IP AS MCR_payment_AB,
		BENE_payment_IP AS BENE_payment_AB,
		MCR_payment_IP AS MCR_payment_A,
		BENE_payment_IP AS BENE_payment_A,
		NULL AS MCR_payment_B,
		NULL AS BENE_payment_B,
		MCR_payment_IP AS MCR_payment_ABD,
		BENE_payment_IP AS BENE_payment_ABD,
		MCR_payment_IP AS MCR_payment_IP,
		BENE_payment_IP AS BENE_payment_IP,
		NULL AS MCR_payment_SNF,
		NULL AS BENE_payment_SNF,
		NULL AS MCR_payment_HHA_A,
		NULL AS BENE_payment_HHA_A,
		NULL AS MCR_payment_HHA_B,
		NULL AS BENE_payment_HHA_B,
		NULL AS MCR_payment_hospice,
		NULL AS BENE_payment_hospice,
		NULL AS MCR_payment_carrier,
		NULL AS BENE_payment_carrier,
		NULL AS MCR_payment_DME,
		NULL AS BENE_payment_DME,
		NULL AS MCR_payment_CBP,
		NULL AS BENE_payment_CBP,
		NULL AS MCR_payment_nCBP,
		NULL AS BENE_payment_nCBP,
		NULL AS MCR_payment_OP,
		NULL AS BENE_payment_OP,
		NULL AS MCR_payment_PDE,
		NULL AS BENE_payment_PDE,
	FROM (
		SELECT 
			BENE_ID AS BENE_ID,
			year AS year,
			month AS month,
			SUM(MCR_payment) AS MCR_payment_IP,
			SUM(BENE_payment) AS BENE_payment_IP,
		FROM (
			SELECT
				BENE_ID AS BENE_ID,
				MEDPAR_YR_NUM AS year,
				EXTRACT(MONTH FROM DSCHRG_DT) AS month,
				MDCR_PMT_AMT+PASS_THRU_AMT AS MCR_payment,
				BENE_IP_DDCTBL_AMT+BENE_PTA_COINSRNC_AMT+BENE_BLOOD_DDCTBL_AMT AS BENE_payment,
			FROM
				`medpar`
			WHERE 
				NCH_CLM_TYPE_CD='60' AND (MEDPAR_YR_NUM=2010 OR MEDPAR_YR_NUM=2015)
		)
		GROUP BY 
			BENE_ID, year, month	
	)
	UNION ALL
	SELECT 
		BENE_ID AS BENE_ID,
		year AS year,
		month AS month,
		MCR_payment_SNF AS MCR_payment_AB,
		BENE_payment_SNF AS BENE_payment_AB,
		MCR_payment_SNF AS MCR_payment_A,
		BENE_payment_SNF AS BENE_payment_A,
		NULL AS MCR_payment_B,
		NULL AS BENE_payment_B,
		MCR_payment_SNF AS MCR_payment_ABD,
		BENE_payment_SNF AS BENE_payment_ABD,
		NULL AS MCR_payment_IP,
		NULL AS BENE_payment_IP,
		MCR_payment_SNF AS MCR_payment_SNF,
		BENE_payment_SNF AS BENE_payment_SNF,
		NULL AS MCR_payment_HHA_A,
		NULL AS BENE_payment_HHA_A,
		NULL AS MCR_payment_HHA_B,
		NULL AS BENE_payment_HHA_B,
		NULL AS MCR_payment_hospice,
		NULL AS BENE_payment_hospice,
		NULL AS MCR_payment_carrier,
		NULL AS BENE_payment_carrier,
		NULL AS MCR_payment_DME,
		NULL AS BENE_payment_DME,
		NULL AS MCR_payment_CBP,
		NULL AS BENE_payment_CBP,
		NULL AS MCR_payment_nCBP,
		NULL AS BENE_payment_nCBP,
		NULL AS MCR_payment_OP,
		NULL AS BENE_payment_OP,
		NULL AS MCR_payment_PDE,
		NULL AS BENE_payment_PDE,
	FROM (
		SELECT 
			BENE_ID AS BENE_ID,
			year AS year,
			month AS month,
			SUM(MCR_payment) AS MCR_payment_SNF,
			SUM(BENE_payment) AS BENE_payment_SNF,
		FROM (
			SELECT
				BENE_ID AS BENE_ID,
				MEDPAR_YR_NUM AS year,
				EXTRACT(MONTH FROM DSCHRG_DT) AS month,
				MDCR_PMT_AMT+PASS_THRU_AMT AS MCR_payment,
				BENE_IP_DDCTBL_AMT+BENE_PTA_COINSRNC_AMT+BENE_BLOOD_DDCTBL_AMT AS BENE_payment,
			FROM
				`medpar`
			WHERE 
				(NCH_CLM_TYPE_CD='20' OR NCH_CLM_TYPE_CD='30') AND (MEDPAR_YR_NUM=2010 OR MEDPAR_YR_NUM=2015)
		)
		GROUP BY 
			BENE_ID, year, month
	)	
	UNION ALL
	SELECT 
		BENE_ID AS BENE_ID,
		year AS year,
		month AS month,
		MCR_payment_HHA_A AS MCR_payment_AB,
		BENE_payment_HHA_A AS BENE_payment_AB,
		MCR_payment_HHA_A AS MCR_payment_A,
		BENE_payment_HHA_A AS BENE_payment_A,
		NULL AS MCR_payment_B,
		NULL AS BENE_payment_B,
		MCR_payment_HHA_A AS MCR_payment_ABD,
		BENE_payment_HHA_A AS BENE_payment_ABD,
		NULL AS MCR_payment_IP,
		NULL AS BENE_payment_IP,
		NULL AS MCR_payment_SNF,
		NULL AS BENE_payment_SNF,
		MCR_payment_HHA_A AS MCR_payment_HHA_A,
		BENE_payment_HHA_A AS BENE_payment_HHA_A,
		NULL AS MCR_payment_HHA_B,
		NULL AS BENE_payment_HHA_B,
		NULL AS MCR_payment_hospice,
		NULL AS BENE_payment_hospice,
		NULL AS MCR_payment_carrier,
		NULL AS BENE_payment_carrier,
		NULL AS MCR_payment_DME,
		NULL AS BENE_payment_DME,
		NULL AS MCR_payment_CBP,
		NULL AS BENE_payment_CBP,
		NULL AS MCR_payment_nCBP,
		NULL AS BENE_payment_nCBP,
		NULL AS MCR_payment_OP,
		NULL AS BENE_payment_OP,
		NULL AS MCR_payment_PDE,
		NULL AS BENE_payment_PDE,
	FROM (
		SELECT 
			BENE_ID AS BENE_ID,
			year AS year,
			month AS month,
			SUM(MCR_payment) AS MCR_payment_HHA_A,
			SUM(BENE_payment) AS BENE_payment_HHA_A,
		FROM (
			SELECT
				BENE_ID AS BENE_ID,
				EXTRACT(YEAR FROM CLM_THRU_DT) AS year,
				EXTRACT(MONTH FROM CLM_THRU_DT) AS month,
				CLM_PMT_AMT AS MCR_payment,
				0 AS BENE_payment,
			FROM
				`home_health_agency_hha_claims`
			WHERE 
				CLM_SRVC_CLSFCTN_TYPE_CD=3 AND (EXTRACT(YEAR FROM CLM_THRU_DT)=2010 OR EXTRACT(YEAR FROM CLM_THRU_DT)=2015)
		)
		GROUP BY 
			BENE_ID, year, month
	)
	UNION ALL
	SELECT 
		BENE_ID AS BENE_ID,
		year AS year,
		month AS month,
		MCR_payment_HHA_B AS MCR_payment_AB,
		BENE_payment_HHA_B AS BENE_payment_AB,
		NULL AS MCR_payment_A,
		NULL AS BENE_payment_A,
		MCR_payment_HHA_B AS MCR_payment_B,
		BENE_payment_HHA_B AS BENE_payment_B,
		MCR_payment_HHA_B AS MCR_payment_ABD,
		BENE_payment_HHA_B AS BENE_payment_ABD,
		NULL AS MCR_payment_IP,
		NULL AS BENE_payment_IP,
		NULL AS MCR_payment_SNF,
		NULL AS BENE_payment_SNF,
		NULL AS MCR_payment_HHA_A,
		NULL AS BENE_payment_HHA_A,
		MCR_payment_HHA_B AS MCR_payment_HHA_B,
		BENE_payment_HHA_B AS BENE_payment_HHA_B,
		NULL AS MCR_payment_hospice,
		NULL AS BENE_payment_hospice,
		NULL AS MCR_payment_carrier,
		NULL AS BENE_payment_carrier,
		NULL AS MCR_payment_DME,
		NULL AS BENE_payment_DME,
		NULL AS MCR_payment_CBP,
		NULL AS BENE_payment_CBP,
		NULL AS MCR_payment_nCBP,
		NULL AS BENE_payment_nCBP,
		NULL AS MCR_payment_OP,
		NULL AS BENE_payment_OP,
		NULL AS MCR_payment_PDE,
		NULL AS BENE_payment_PDE,
	FROM (
		SELECT 
			BENE_ID AS BENE_ID,
			year AS year,
			month AS month,
			SUM(MCR_payment) AS MCR_payment_HHA_B,
			SUM(BENE_payment) AS BENE_payment_HHA_B,
		FROM (
			SELECT
				BENE_ID AS BENE_ID,
				EXTRACT(YEAR FROM CLM_THRU_DT) AS year,
				EXTRACT(MONTH FROM CLM_THRU_DT) AS month,
				CLM_PMT_AMT AS MCR_payment,
				0 AS BENE_payment,
			FROM
				`home_health_agency_hha_claims`
			WHERE 
				CLM_SRVC_CLSFCTN_TYPE_CD=2 AND (EXTRACT(YEAR FROM CLM_THRU_DT)=2010 OR EXTRACT(YEAR FROM CLM_THRU_DT)=2015)
		)
		GROUP BY 
			BENE_ID, year, month
	)
	UNION ALL
	SELECT 
		BENE_ID AS BENE_ID,
		year AS year,
		month AS month,
		MCR_payment_hospice AS MCR_payment_AB,
		BENE_payment_hospice AS BENE_payment_AB,
		MCR_payment_hospice AS MCR_payment_A,
		BENE_payment_hospice AS BENE_payment_A,
		NULL AS MCR_payment_B,
		NULL AS BENE_payment_B,
		MCR_payment_hospice AS MCR_payment_ABD,
		BENE_payment_hospice AS BENE_payment_ABD,
		NULL AS MCR_payment_IP,
		NULL AS BENE_payment_IP,
		NULL AS MCR_payment_SNF,
		NULL AS BENE_payment_SNF,
		NULL AS MCR_payment_HHA_A,
		NULL AS BENE_payment_HHA_A,
		NULL AS MCR_payment_HHA_B,
		NULL AS BENE_payment_HHA_B,
		MCR_payment_hospice AS MCR_payment_hospice,
		BENE_payment_hospice AS BENE_payment_hospice,
		NULL AS MCR_payment_carrier,
		NULL AS BENE_payment_carrier,
		NULL AS MCR_payment_DME,
		NULL AS BENE_payment_DME,
		NULL AS MCR_payment_CBP,
		NULL AS BENE_payment_CBP,
		NULL AS MCR_payment_nCBP,
		NULL AS BENE_payment_nCBP,
		NULL AS MCR_payment_OP,
		NULL AS BENE_payment_OP,
		NULL AS MCR_payment_PDE,
		NULL AS BENE_payment_PDE,
	FROM (
		SELECT 
			BENE_ID AS BENE_ID,
			year AS year,
			month AS month,
			SUM(MCR_payment) AS MCR_payment_hospice,
			SUM(BENE_payment) AS BENE_payment_hospice,
		FROM (
			SELECT
				BENE_ID AS BENE_ID,
				EXTRACT(YEAR FROM CLM_THRU_DT) AS year,
				EXTRACT(MONTH FROM CLM_THRU_DT) AS month,
				CLM_PMT_AMT AS MCR_payment,
				0 AS BENE_payment,
			FROM
				`hospice_base_claims`
			WHERE 
				(EXTRACT(YEAR FROM CLM_THRU_DT)=2010 OR EXTRACT(YEAR FROM CLM_THRU_DT)=2015)
		)
		GROUP BY 
			BENE_ID, year, month
	)
	UNION ALL
	SELECT 
		BENE_ID AS BENE_ID,
		year AS year,
		month AS month,
		MCR_payment_carrier AS MCR_payment_AB,
		BENE_payment_carrier AS BENE_payment_AB,
		NULL AS MCR_payment_A,
		NULL AS BENE_payment_A,
		MCR_payment_carrier AS MCR_payment_B,
		BENE_payment_carrier AS BENE_payment_B,
		MCR_payment_carrier AS MCR_payment_ABD,
		BENE_payment_carrier AS BENE_payment_ABD,
		NULL AS MCR_payment_IP,
		NULL AS BENE_payment_IP,
		NULL AS MCR_payment_SNF,
		NULL AS BENE_payment_SNF,
		NULL AS MCR_payment_HHA_A,
		NULL AS BENE_payment_HHA_A,
		NULL AS MCR_payment_HHA_B,
		NULL AS BENE_payment_HHA_B,
		NULL AS MCR_payment_hospice,
		NULL AS BENE_payment_hospice,
		MCR_payment_carrier AS MCR_payment_carrier,
		BENE_payment_carrier AS BENE_payment_carrier,
		NULL AS MCR_payment_DME,
		NULL AS BENE_payment_DME,
		NULL AS MCR_payment_CBP,
		NULL AS BENE_payment_CBP,
		NULL AS MCR_payment_nCBP,
		NULL AS BENE_payment_nCBP,
		NULL AS MCR_payment_OP,
		NULL AS BENE_payment_OP,
		NULL AS MCR_payment_PDE,
		NULL AS BENE_payment_PDE,
	FROM (
		SELECT 
			BENE_ID AS BENE_ID,
			year AS year,
			month AS month,
			SUM(MCR_payment) AS MCR_payment_carrier,
			SUM(BENE_payment) AS BENE_payment_carrier,
		FROM (
			SELECT
				BENE_ID AS BENE_ID,
				EXTRACT(YEAR FROM CLM_THRU_DT) AS year,
				EXTRACT(MONTH FROM CLM_THRU_DT) AS month,
				LINE_NCH_PMT_AMT AS MCR_payment,
				LINE_BENE_PTB_DDCTBL_AMT+LINE_COINSRNC_AMT AS BENE_payment,
			FROM
				`carrier_line_file`
			WHERE 
				NCH_CLM_TYPE_CD=71 AND (EXTRACT(YEAR FROM CLM_THRU_DT)=2010 OR EXTRACT(YEAR FROM CLM_THRU_DT)=2015)
			UNION ALL 
			SELECT
				BENE_ID AS BENE_ID,
				EXTRACT(YEAR FROM CLM_THRU_DT) AS year,
				EXTRACT(MONTH FROM CLM_THRU_DT) AS month,
				LINE_NCH_PMT_AMT AS MCR_payment,
				LINE_BENE_PTB_DDCTBL_AMT+LINE_COINSRNC_AMT AS BENE_payment,
			FROM
				`durable_medical_equipment_dme_line_file`
			WHERE 
				NCH_CLM_TYPE_CD=81 AND (EXTRACT(YEAR FROM CLM_THRU_DT)=2010 OR EXTRACT(YEAR FROM CLM_THRU_DT)=2015)
		)
		GROUP BY 
			BENE_ID, year, month
	)
	UNION ALL
	SELECT 
		BENE_ID AS BENE_ID,
		year AS year,
		month AS month,
		MCR_payment_DME AS MCR_payment_AB,
		BENE_payment_DME AS BENE_payment_AB,
		NULL AS MCR_payment_A,
		NULL AS BENE_payment_A,
		MCR_payment_DME AS MCR_payment_B,
		BENE_payment_DME AS BENE_payment_B,
		MCR_payment_DME AS MCR_payment_ABD,
		BENE_payment_DME AS BENE_payment_ABD,
		NULL AS MCR_payment_IP,
		NULL AS BENE_payment_IP,
		NULL AS MCR_payment_SNF,
		NULL AS BENE_payment_SNF,
		NULL AS MCR_payment_HHA_A,
		NULL AS BENE_payment_HHA_A,
		NULL AS MCR_payment_HHA_B,
		NULL AS BENE_payment_HHA_B,
		NULL AS MCR_payment_hospice,
		NULL AS BENE_payment_hospice,
		NULL AS MCR_payment_carrier,
		NULL AS BENE_payment_carrier,
		MCR_payment_DME AS MCR_payment_DME,
		BENE_payment_DME AS BENE_payment_DME,
		MCR_payment_CBP AS MCR_payment_CBP,
		BENE_payment_CBP AS BENE_payment_CBP,
		MCR_payment_nCBP AS MCR_payment_nCBP,
		BENE_payment_nCBP AS BENE_payment_nCBP,
		NULL AS MCR_payment_OP,
		NULL AS BENE_payment_OP,
		NULL AS MCR_payment_PDE,
		NULL AS BENE_payment_PDE,
	FROM (
		SELECT 
			BENE_ID AS BENE_ID,
			year AS year,
			month AS month,
			SUM(MCR_payment) AS MCR_payment_DME,
			SUM(MCR_payment_CBP) AS MCR_payment_CBP,
			SUM(MCR_payment_nCBP) AS MCR_payment_nCBP,
			SUM(BENE_payment) AS BENE_payment_DME,
			SUM(BENE_payment_CBP) AS BENE_payment_CBP,
			SUM(BENE_payment_nCBP) AS BENE_payment_nCBP
		FROM
		(
			SELECT 
				t0.BENE_ID AS BENE_ID,
				t0.year AS year,
				t0.month AS month,
				t0.MCR_payment AS MCR_payment,
				CASE WHEN t1.category_x IS NOT NULL THEN t0.MCR_payment ELSE NULL END AS MCR_payment_CBP,
				CASE WHEN t1.category_x IS NULL THEN t0.MCR_payment ELSE NULL END AS MCR_payment_nCBP,
				t0.BENE_payment AS BENE_payment,
				CASE WHEN t1.category_x IS NOT NULL THEN t0.BENE_payment ELSE NULL END AS BENE_payment_CBP,
				CASE WHEN t1.category_x IS NULL THEN t0.BENE_payment ELSE NULL END AS BENE_payment_nCBP,
			FROM (
				SELECT
					BENE_ID AS BENE_ID,
					EXTRACT(YEAR FROM CLM_THRU_DT) AS year,
					EXTRACT(MONTH FROM CLM_THRU_DT) AS month,
					LINE_NCH_PMT_AMT AS MCR_payment,
					LINE_BENE_PTB_DDCTBL_AMT+LINE_COINSRNC_AMT AS BENE_payment,
					HCPCS_CD AS HCPCS_CD,
					HCPCS_1ST_MDFR_CD AS HCPCS_1ST_MDFR_CD,
					HCPCS_2ND_MDFR_CD AS HCPCS_2ND_MDFR_CD,
				FROM
					`carrier_line_file`
				WHERE 
					NCH_CLM_TYPE_CD=72 AND (EXTRACT(YEAR FROM CLM_THRU_DT)=2010 OR EXTRACT(YEAR FROM CLM_THRU_DT)=2015)
				UNION ALL 
				SELECT
					BENE_ID AS BENE_ID,
					EXTRACT(YEAR FROM CLM_THRU_DT) AS year,
					EXTRACT(MONTH FROM CLM_THRU_DT) AS month,
					LINE_NCH_PMT_AMT AS MCR_payment,
					LINE_BENE_PTB_DDCTBL_AMT+LINE_COINSRNC_AMT AS BENE_payment,
					HCPCS_CD AS HCPCS_CD,
					HCPCS_1ST_MDFR_CD AS HCPCS_1ST_MDFR_CD,
					HCPCS_2ND_MDFR_CD AS HCPCS_2ND_MDFR_CD,
				FROM
					`durable_medical_equipment_dme_line_file`
				WHERE 
					NCH_CLM_TYPE_CD=82 AND (EXTRACT(YEAR FROM CLM_THRU_DT)=2010 OR EXTRACT(YEAR FROM CLM_THRU_DT)=2015)
			) AS t0
			LEFT JOIN `hcpcs_list_matched` AS t1
				ON t0.HCPCS_CD = t1.HCPCS_CD AND IFNULL(t0.HCPCS_1ST_MDFR_CD,'.') = IFNULL(t1.HCPCS_1ST_MDFR_CD,'.') 
					AND IFNULL(t0.HCPCS_2ND_MDFR_CD,'.') = IFNULL(t1.HCPCS_2ND_MDFR_CD,'.')
		)
		GROUP BY 
			BENE_ID, year, month
	)
	UNION ALL
	SELECT 
		BENE_ID AS BENE_ID,
		year AS year,
		month AS month,
		MCR_payment_OP AS MCR_payment_AB,
		BENE_payment_OP AS BENE_payment_AB,
		NULL AS MCR_payment_A,
		NULL AS BENE_payment_A,
		MCR_payment_OP AS MCR_payment_B,
		BENE_payment_OP AS BENE_payment_B,
		MCR_payment_OP AS MCR_payment_ABD,
		BENE_payment_OP AS BENE_payment_ABD,
		NULL AS MCR_payment_IP,
		NULL AS BENE_payment_IP,
		NULL AS MCR_payment_SNF,
		NULL AS BENE_payment_SNF,
		NULL AS MCR_payment_HHA_A,
		NULL AS BENE_payment_HHA_A,
		NULL AS MCR_payment_HHA_B,
		NULL AS BENE_payment_HHA_B,
		NULL AS MCR_payment_hospice,
		NULL AS BENE_payment_hospice,
		NULL AS MCR_payment_carrier,
		NULL AS BENE_payment_carrier,
		NULL AS MCR_payment_DME,
		NULL AS BENE_payment_DME,
		NULL AS MCR_payment_CBP,
		NULL AS BENE_payment_CBP,
		NULL AS MCR_payment_nCBP,
		NULL AS BENE_payment_nCBP,
		MCR_payment_OP AS MCR_payment_OP,
		BENE_payment_OP AS BENE_payment_OP,
		NULL AS MCR_payment_PDE,
		NULL AS BENE_payment_PDE,
	FROM (
		SELECT 
			BENE_ID AS BENE_ID,
			year AS year,
			month AS month,
			SUM(MCR_payment) AS MCR_payment_OP,
			SUM(BENE_payment) AS BENE_payment_OP,
		FROM (
			SELECT
				t0.BENE_ID AS BENE_ID,
				EXTRACT(YEAR FROM t0.CLM_THRU_DT) AS year,
				EXTRACT(MONTH FROM t0.CLM_THRU_DT) AS month,
				t1.REV_CNTR_PMT_AMT_AMT AS MCR_payment,
				t1.REV_CNTR_CASH_DDCTBL_AMT+t1.REV_CNTR_BLOOD_DDCTBL_AMT+t1.REV_CNTR_COINSRNC_WGE_ADJSTD_C-t1.REV_CNTR_RDCD_COINSRNC_AMT AS BENE_payment,
			FROM
				`outpatient_base_claim_file` AS t0
			LEFT JOIN `outpatient_revenue_center_file` AS t1
				ON t0.CLM_ID = t1.CLM_ID
			WHERE 
				(EXTRACT(YEAR FROM t0.CLM_THRU_DT)=2010 OR EXTRACT(YEAR FROM t0.CLM_THRU_DT)=2015)
		)
		GROUP BY 
			BENE_ID, year, month
	)	
	UNION ALL
	SELECT 
		BENE_ID AS BENE_ID,
		year AS year,
		month AS month,
		NULL AS MCR_payment_AB,
		NULL AS BENE_payment_AB,
		NULL AS MCR_payment_A,
		NULL AS BENE_payment_A,
		NULL AS MCR_payment_B,
		NULL AS BENE_payment_B,
		MCR_payment_PDE AS MCR_payment_ABD,
		BENE_payment_PDE AS BENE_payment_ABD,
		NULL AS MCR_payment_IP,
		NULL AS BENE_payment_IP,
		NULL AS MCR_payment_SNF,
		NULL AS BENE_payment_SNF,
		NULL AS MCR_payment_HHA_A,
		NULL AS BENE_payment_HHA_A,
		NULL AS MCR_payment_HHA_B,
		NULL AS BENE_payment_HHA_B,
		NULL AS MCR_payment_hospice,
		NULL AS BENE_payment_hospice,
		NULL AS MCR_payment_carrier,
		NULL AS BENE_payment_carrier,
		NULL AS MCR_payment_DME,
		NULL AS BENE_payment_DME,
		NULL AS MCR_payment_CBP,
		NULL AS BENE_payment_CBP,
		NULL AS MCR_payment_nCBP,
		NULL AS BENE_payment_nCBP,
		NULL AS MCR_payment_OP,
		NULL AS BENE_payment_OP,
		MCR_payment_PDE AS MCR_payment_PDE,
		BENE_payment_PDE AS BENE_payment_PDE,
	FROM (
		SELECT 
			BENE_ID AS BENE_ID,
			year AS year,
			month AS month,
			SUM(MCR_payment) AS MCR_payment_PDE,
			SUM(BENE_payment) AS BENE_payment_PDE,
		FROM (
			SELECT
				BENE_ID AS BENE_ID,
				EXTRACT(YEAR FROM SRVC_DT) AS year,
				EXTRACT(MONTH FROM SRVC_DT) AS month,
				CVRD_D_PLAN_PD_AMT+NCVRD_PLAN_PD_AMT AS MCR_payment,
				PTNT_PAY_AMT AS BENE_payment,
			FROM
				`part_d_event_pde_file`
			WHERE 
				(EXTRACT(YEAR FROM SRVC_DT)=2010 OR EXTRACT(YEAR FROM SRVC_DT)=2015)
		)
		GROUP BY 
			BENE_ID, year, month
	)	
	) AS t0
	RIGHT JOIN (
		SELECT 
			BENE_ID AS BENE_ID,
			year AS year,
			month AS month,
			1 AS FFS,
		FROM (
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 1 AS month, BENE_MDCR_ENTLMT_BUYIN_IND_01 AS MDCR, BENE_HMO_IND_01 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR<=2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 2 AS month, BENE_MDCR_ENTLMT_BUYIN_IND_02 AS MDCR, BENE_HMO_IND_02 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR<=2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 3 AS month, BENE_MDCR_ENTLMT_BUYIN_IND_03 AS MDCR, BENE_HMO_IND_03 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR<=2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 4 AS month, BENE_MDCR_ENTLMT_BUYIN_IND_04 AS MDCR, BENE_HMO_IND_04 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR<=2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 5 AS month, BENE_MDCR_ENTLMT_BUYIN_IND_05 AS MDCR, BENE_HMO_IND_05 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR<=2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 6 AS month, BENE_MDCR_ENTLMT_BUYIN_IND_06 AS MDCR, BENE_HMO_IND_06 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR<=2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 7 AS month, BENE_MDCR_ENTLMT_BUYIN_IND_07 AS MDCR, BENE_HMO_IND_07 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR<=2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 8 AS month, BENE_MDCR_ENTLMT_BUYIN_IND_08 AS MDCR, BENE_HMO_IND_08 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR<=2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 9 AS month, BENE_MDCR_ENTLMT_BUYIN_IND_09 AS MDCR, BENE_HMO_IND_09 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR<=2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 10 AS month, BENE_MDCR_ENTLMT_BUYIN_IND_10 AS MDCR, BENE_HMO_IND_10 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR<=2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 11 AS month, BENE_MDCR_ENTLMT_BUYIN_IND_11 AS MDCR, BENE_HMO_IND_11 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR<=2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 12 AS month, BENE_MDCR_ENTLMT_BUYIN_IND_12 AS MDCR, BENE_HMO_IND_12 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR<=2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 1 AS month, MDCR_ENTLMT_BUYIN_IND_01 AS MDCR, HMO_IND_01 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR>2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 2 AS month, MDCR_ENTLMT_BUYIN_IND_02 AS MDCR, HMO_IND_02 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR>2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 3 AS month, MDCR_ENTLMT_BUYIN_IND_03 AS MDCR, HMO_IND_03 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR>2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 4 AS month, MDCR_ENTLMT_BUYIN_IND_04 AS MDCR, HMO_IND_04 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR>2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 5 AS month, MDCR_ENTLMT_BUYIN_IND_05 AS MDCR, HMO_IND_05 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR>2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 6 AS month, MDCR_ENTLMT_BUYIN_IND_06 AS MDCR, HMO_IND_06 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR>2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 7 AS month, MDCR_ENTLMT_BUYIN_IND_07 AS MDCR, HMO_IND_07 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR>2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 8 AS month, MDCR_ENTLMT_BUYIN_IND_08 AS MDCR, HMO_IND_08 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR>2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 9 AS month, MDCR_ENTLMT_BUYIN_IND_09 AS MDCR, HMO_IND_09 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR>2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 10 AS month, MDCR_ENTLMT_BUYIN_IND_10 AS MDCR, HMO_IND_10 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR>2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 11 AS month, MDCR_ENTLMT_BUYIN_IND_11 AS MDCR, HMO_IND_11 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR>2013
			UNION ALL
			SELECT BENE_ID AS BENE_ID, BENE_ENROLLMT_REF_YR AS year, 12 AS month, MDCR_ENTLMT_BUYIN_IND_12 AS MDCR, HMO_IND_12 AS HMO FROM `master_beneficiary_summary_file_mbsf_base_a_b_c_d` WHERE BENE_ENROLLMT_REF_YR>2013
		)
		WHERE MDCR<>'0' AND HMO='0'
	) AS t1
		ON t0.BENE_ID = t1.BENE_ID AND t0.year = t1.year AND t0.month = t1.month
	GROUP BY t0.BENE_ID, t0.year
)
GROUP BY year
)
















